SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[MVARollup] ( @Date DATETIME)
RETURNS 

@results TABLE(
SSB_Cusip VARCHAR(50),
AssetName VARCHAR(100),
ArtyPricingID VARCHAR(50),
Par DECIMAL (38,2),
AmortizedCost DECIMAL(38,2),
MarketValue DECIMAL(38,2)

)
AS BEGIN

declare 
@rollup	TABLE( [FUND NUMBER] [nvarchar](255) NULL,
	[FUND NAME] [nvarchar](255) NULL,
	[MANAGEMENT NAME] [nvarchar](255) NULL,
	[BASE CURRENCY CODE] [nvarchar](255) NULL,
	[PAGE NUMBER] [float] NULL,
	[AS-OF DATE] [datetime] NULL,
	[REPORT TITLE] [nvarchar](255) NULL,
	[SECURITY TYPE] [nvarchar](255) NULL,
	[SECURITY CATEGORY/ACTION] [nvarchar](255) NULL,
	[LOCAL CURRENCY] [nvarchar](255) NULL,
	[TRADE COUNTRY] [nvarchar](255) NULL,
	[EXCHANGE RATE] [nvarchar](255) NULL,
	[MAJOR INDUSTRY] [nvarchar](255) NULL,
	[MINOR INDUSTRY] [nvarchar](255) NULL,
	[TICKER] [nvarchar](255) NULL,
	[ASSET ID] [nvarchar](255) NULL,
	[ASSET DESCRIPTION] [nvarchar](255) NULL,
	[COUPON RATE] [nvarchar](255) NULL,
	[MATURITY DATE] [nvarchar](255) NULL,
	[STRIKE PRICE] [nvarchar](255) NULL,
	[CALL/PUT INDICATOR] [nvarchar](255) NULL,
	[ISSUE DESCRIPTION] [nvarchar](255) NULL,
	[EXPIRATION DATE] [nvarchar](255) NULL,
	[UNITS] [float] NULL,
	[LOCAL UNIT COST] [float] NULL,
	[LOCAL TOTAL COST] [float] NULL,
	[LOCAL UNIT PRICE] [float] NULL,
	[LOCAL MARKET VALUE] [float] NULL,
	[% OF CURRENCY] [float] NULL,
	[LOCAL UNREALIZED GAIN LOSS] [float] NULL,
	[UNDERLYING FUND #] [nvarchar](255) NULL,
	[ORIGINAL FACE (UNITS)] [float] NULL,
	[BASE UNIT COST] [float] NULL,
	[BASE TOTAL COST] [float] NULL,
	[BASE UNIT PRICE] [float] NULL,
	[BASE MARKET VALUE] [float] NULL,
	[% FUND] [float] NULL,
	[BASE UNREALIZED GAIN LOSS] [float] NULL,
	[App] [float] NULL,
	[Dep] [float] NULL,
	ArtemisID VARCHAR(50))

	
	
 INSERT INTO @rollup

SELECT  mva.[FUND NUMBER] ,
		mva.[FUND NAME] ,
		mva.[MANAGEMENT NAME] ,
		mva.[BASE CURRENCY CODE] ,
		mva.[PAGE NUMBER] ,
		mva.[AS-OF DATE] ,
		mva.[REPORT TITLE] ,
		mva.[SECURITY TYPE] ,
		mva.[SECURITY CATEGORY/ACTION] ,
		mva.[LOCAL CURRENCY] ,
		mva.[TRADE COUNTRY] ,
		mva.[EXCHANGE RATE] ,
		mva.[MAJOR INDUSTRY] ,
		mva.[MINOR INDUSTRY] ,
		mva.TICKER ,
		COALESCE(dup.ssbGroupedCusip, mva.[ASSET ID]) [ASSET ID],
		mva.[ASSET DESCRIPTION] ,
		mva.[COUPON RATE] ,
		mva.[MATURITY DATE] ,
		mva.[STRIKE PRICE] ,
		mva.[CALL/PUT INDICATOR] ,
		mva.[ISSUE DESCRIPTION] ,
		mva.[EXPIRATION DATE] ,
		mva.Units,
		mva.[LOCAL UNIT COST] ,
		mva.[LOCAL TOTAL COST] ,
		mva.[LOCAL UNIT PRICE]   ,
		mva.[LOCAL MARKET VALUE] ,
		mva.[% OF CURRENCY] ,
		mva.[LOCAL UNREALIZED GAIN LOSS] ,
		mva.[UNDERLYING FUND #] ,
		mva.[ORIGINAL FACE (UNITS)] ,
		mva.[BASE UNIT COST] ,
		mva.[BASE TOTAL COST] ,
		mva.[BASE UNIT PRICE] ,
		mva.[BASE MARKET VALUE] ,
		mva.[% FUND] ,
		mva.[BASE UNREALIZED GAIN LOSS] ,
		mva.App ,

		mva.Dep,
		dup.FS_ID
		
		 FROM dbo.MVADate  mva

		--INNER JOIN  ReportingScripts.dbo.Duplicate_SSB_CUSIPS dup
		--ON dup.SSB_Unique_Cusip = mva.[ASSET ID]

CROSS APPLY (

SELECT DISTINCT dm.ssbGroupedCusip, FS_ID FROM dbo.Duplicate_SSB_CUSIPS dm
WHERE dm.SSB_Unique_Cusip = mva.[ASSET ID]
 ) dup


		--CROSS APPLY (SELECT  dup.FS_ID ,
		--        dup.SSB_Unique_Cusip ,
		--        dup.ssbGroupedCusip FROM  ReportingScripts.dbo.Duplicate_SSB_CUSIPS dup WHERE dup.SSB_Unique_Cusip = mva.[ASSET ID]) dups

		WHERE   mva.[AS-OF DATE] = 	(SELECT MAX([AS-OF DATE]) FROM dbo.MVADate)
		ORDER BY mva.[ASSET ID] ASC
		--AND mva.[ASSET ID] = dups.SSB_Unique_Cusip
		
		INSERT INTO @results
		        ( ArtyPricingID ,
		          SSB_Cusip ,
				  AssetName,
		          Par ,
		          MarketValue ,
		          AmortizedCost
	)

	SELECT DISTINCT MAX(roll.ArtemisID), roll.[ASSET ID], 
	MAX(roll.[ASSET DESCRIPTION]),
	
	SUM(roll.UNITS)					,
	SUM(roll.[BASE MARKET VALUE])	,
	SUM(roll.[BASE TOTAL COST])		
	
	--	OVER(PARTITION BY roll.[ASSET ID]) AS par,
	--OVER(PARTITION BY roll.[ASSET ID] ) AS mv,
	-- OVER(PARTITION BY roll.[ASSET ID] ) AS ac 
	

	FROM @rollup roll
	GROUP BY roll.[ASSET ID]
	ORDER BY roll.[ASSET ID]
	
	RETURN;

		END;	

	
GO
